package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.PageModel;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据实现层 ---跟数据库打交道
 */
public class UserImpl implements UserDao {
    @Override
    public UserModel findByUsernameAndPassword(UserModel model) {
        String sql = "select * from user where username='" + model.getUsername() + "' and password='" + model.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);
        UserModel userModel = null;
        try {
            while (resultSet.next()) {
                userModel = new UserModel();
//                userModel.setId(resultSet.getInt("id"));
//                userModel.setUsername(resultSet.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select * from user  where 1=1 ";
        sql += setSql(userModel);
        sql += " order by id desc ";
        sql += " limit " + (userModel.getPageNo() - 1) * userModel.getPageSize() + ", " + userModel.getPageSize() + "";
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setAddress(resultSet.getString("address"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setEmail(resultSet.getString("email"));
                model.setEnable(resultSet.getInt("enable"));
                model.setUpdateTime(resultSet.getString("update_time"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setSex(resultSet.getString("sex"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int add(UserModel model) {
        if ("".equals(model.getEmail()) || model.getEmail() == null) {
            model.setEmail("");
        }
        if ("".equals(model.getAddress()) || model.getAddress() == null) {
            model.setAddress("");
        }
        String sql = "INSERT INTO `user` (`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`," +
                " `phone`, `sex`, `update_time`," +
                " `hobby`, `birthday`) VALUES (" +
                "'" + model.getUsername() + "', '" + model.getAddress() + "', now()," +
                " '" + model.getEmail() + "', '" + model.getEnable() + "', '" + model.getPassword() + "'," +
                " '" + model.getPhone() + "', '" + model.getSex() + "', now()," +
                " '" + model.getHobby() + "', '" + model.getBirthday() + "')";

        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel model) {
        String sql = "UPDATE `user` SET ";
        if (model.getUsername() != null && !"".equals(model.getUsername())) {
            sql += "`username` = '" + model.getUsername() + "', ";
        }
        if (model.getAddress() != null && !"".equals(model.getAddress())) {
            sql += "`address` = '" + model.getAddress() + "', ";
        }
        if (model.getEmail() != null && !"".equals(model.getEmail())) {
            sql += "`email` = '" + model.getEmail() + "', ";
        }
        if (model.getEnable() != null) {
            sql += "`enable` = '" + model.getEnable() + "', ";
        }
        if (model.getPassword() != null && !"".equals(model.getPassword())) {
            sql += "`password` = '" + model.getPassword() + "', ";
        }
        if (model.getPhone() != null && !"".equals(model.getPhone())) {
            sql += "`phone` = '" + model.getPhone() + "',";
        }
        if (model.getSex() != null && !"".equals(model.getSex())) {
            sql += " `sex` = '" + model.getSex() + "',";
        }
        sql += " `update_time` = now(),";
        if (model.getHobby() != null && !"".equals(model.getHobby())) {
            sql += " `hobby` = '" + model.getHobby() + "', ";
        }
        if (model.getBirthday() != null && !"".equals(model.getBirthday())) {
            sql += " `birthday` = '" + model.getBirthday() + "'";
        }
        sql += " remark='' ";
        sql += " WHERE (`id` = '" + model.getId() + "');";
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select *,date_format(birthday,'%Y-%m-%d')  birthdaya from user where id=" + userModel.getId();
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setAddress(resultSet.getString("address"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setEmail(resultSet.getString("email"));
                model.setEnable(resultSet.getInt("enable"));
                model.setUpdateTime(resultSet.getString("update_time"));
                model.setBirthday(resultSet.getString("birthdaya"));
                model.setHobby(resultSet.getString("hobby"));
                model.setSex(resultSet.getString("sex"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
//        String sql = "delete from user where id=?";
//        String sqla = "select * from user where username=? and id=?";
//        List list = new ArrayList();
//        list.add(userModel.getUsername());
//        list.add(userModel.getId());
//        return JDBC.updatea(sqla,list);
        return JDBC.update(sql);
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user  where 1=1";
        sql += setSql(userModel);
//        只是在查询总条数的时候不要排序和分页
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private String setSql(UserModel userModel) {
        String sql = "";
        if (!"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%'";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += " and phone like '%" + userModel.getPhone() + "%'";
        }
        if (!"".equals(userModel.getSex())) {
            sql += " and sex = '" + userModel.getPhone() + "'";
        }
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql += " and birthday >='" + userModel.getStart() + "'";
        }
        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday <='" + userModel.getEnd() + "'";
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday between '" + userModel.getStart() + "' and '" + userModel.getEnd() + "'";
        }
        if (userModel.getEnable() != -1) {
            sql += " and enable=" + userModel.getEnable();
        }
//        爱好模糊查询
//        select * from user where username='fsadfas' or '1'='1';
//        sql注入的bug
//        select * from user where username=?;
        if (!"".equals(userModel.getHobby())) {
            String hobby = userModel.getHobby();
            String[] hobbys = hobby.split(",");
            sql += " and (";
//            for循环只是需要将前端传递过来的爱好数组分开
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += " find_in_set('" + hobbys[i] + "',`hobby`)  ";
                } else {
                    sql += " or find_in_set('" + hobbys[i] + "',`hobby`)  ";
                }

            }
            sql += ") ";
        }
        return sql;
    }
}
